package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTGuardrailFaultDto;
import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.GuardrailDto;
import com.hualude.roadsign.dto.GuardrailFaultDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by ding on 2017/8/4.
 */
public class GuardrailFaultRepositoryImpl {

    @PersistenceContext
    private EntityManager em;

    public List<DTGuardrailFaultDto> getGuardrailFaultCheck4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT GuardrailFaultId,mf.GuardrailId,mf.GuardrailFaultPhoto,mf.GuardrailFaultCreatorId,mf.GuardrailFaultTime,mf.GuardrailFaultReason," +
                "mf.GuardrailCheckerId,mf.GuardrailMoney,mf.GuardrailFaultLevel,mf.GuardrailFaultType,mf.GuardrailMainCheckerId,mf.GuardrailFeedback,\n" +
                "mf.GuardrailMainCheck,mf.GuardrailCheck,mf.GuardrailOrgId,mf.GuardrailMainOrgId,OrgName AS guardrailOrgName,m.GuardrailPhoto \n" +
                "FROM tb_guardrail_fault mf,tb_guardrail m,tb_org o\n" +
                "WHERE mf.GuardrailOrgId=o.OrgId AND mf.GuardrailId = m.GuardrailId   " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND mf.GuardrailCheck=1 ";
//        根据主管审核人和系统管理员区分
        if (actorId == 7) {
            sql += " and mf.GuardrailMainOrgId=" + orgId + "";
        }
        sql += "  AND  mf.GuardrailMainCheck IN (0,1,-1) \n" +
                " order by  find_in_set( mf.GuardrailMainCheck ,'0,1,-1') , mf.GuardrailFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTGuardrailFaultDto.class));
        List<DTGuardrailFaultDto> lampFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return lampFaultDtos;
    }

    public Integer getGuardrailFaultCheckCount4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT count(*)\n" +
                "FROM tb_guardrail_fault mf,tb_guardrail m,tb_org o\n" +
                "WHERE mf.GuardrailOrgId=o.OrgId AND mf.GuardrailId = m.GuardrailId   " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND mf.GuardrailCheck=1 ";
//        根据主管审核人和系统管理员区分
        if (actorId == 7) {
            sql += " and mf.GuardrailMainOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTGuardrailFaultDto> getGuardrailFaultHandle4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer maintenanceStatus) throws Exception {
        String sql = "SELECT u.userName AS guardrailFaultCreatorName,lf.GuardrailFaultId,OrgName AS guardrailOrgName,lf.GuardrailFaultCreatorId,lf.GuardrailFaultTime,lf.GuardrailCheckerId,lf.GuardrailFaultType,lf.GuardrailFaultLevel,lf.GuardrailMaintenancePhoto,lf.GuardrailMaintainerId,lf.GuardrailFaultReason," +
                "lf.GuardrailMaintenanceStatus,lf.GuardrailMainCheckTime,lf.GuardrailMaintenanceExplain,lf.GuardrailFaultPhoto," +
                "lf.GuardrailMainCheckerId,lf.GuardrailId,lf.GuardrailMaintenanceContent,lf.GuardrailFeedback,sl.GuardrailStartRoad,sl.GuardrailEndRoad,sl.GuardrailRoadName," +
                "sl.GuardrailPhoto,sl.GuardrailArea,sl.GuardrailCity,sl.GuardrailProvince,lf.GuardrailOrgId,lf.guardrailMoney,lf.guardrailMaintenanceTime\n" +
                "FROM tb_guardrail sl,tb_guardrail_fault lf,tb_org o,tb_user u\n" +
                "WHERE sl.GuardrailId=lf.GuardrailId AND lf.GuardrailOrgId=o.OrgId AND u.userId=lf.guardrailFaultCreatorId AND lf.GuardrailMainCheck in (1,2) \n" +
                "AND concat(sl.GuardrailStartRoad, sl.GuardrailEndRoad,sl.GuardrailRoadName) like '%" + dtRequestDto.getSearch() + "%' AND lf.GuardrailCheck in (1,2) ";
        //根据维修人员和系统管理员区分
        if (maintenanceStatus != 3) {
            sql += " and lf.GuardrailMaintenanceStatus = " + maintenanceStatus + " ";
        }
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND lf.GuardrailOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND lf.GuardrailMainOrgId =" + orgId + "";
        }

        sql += " order by lf.GuardrailMaintenanceStatus ASC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTGuardrailFaultDto.class));
        List<DTGuardrailFaultDto> solarFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return solarFaultDtos;
    }

    public Integer getGuardrailFaultHandleCount4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer maintenanceStatus) throws Exception {
        String sql = "SELECT count(*)\n" +
                "FROM tb_guardrail sl,tb_guardrail_fault lf,tb_org o\n" +
                "WHERE sl.GuardrailId=lf.GuardrailId AND lf.GuardrailOrgId=o.OrgId AND lf.GuardrailMainCheck=1 \n" +
                "AND concat(sl.GuardrailStartRoad, sl.GuardrailEndRoad,sl.GuardrailRoadName) like '%" + dtRequestDto.getSearch() + "%' AND lf.GuardrailCheck=1 ";
        //根据维修人员和系统管理员区分
        if (maintenanceStatus != 3) {
            sql += " and lf.GuardrailMaintenanceStatus = " + maintenanceStatus + " ";
        }
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND lf.GuardrailOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND lf.GuardrailMainOrgId =" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<GuardrailFaultDto> getGuardrailFaultCheckList4App(Integer orgId, String actorIds, Integer faultStatus, Integer count, Integer size) throws Exception {
        String sql = " SELECT lf.GuardrailFaultId,lf.GuardrailId,lf.GuardrailFaultPhoto,lf.GuardrailFaultType,lf.GuardrailFaultLevel,lf.GuardrailFaultReason," +
                "lf.GuardrailFaultTime,lf.GuardrailMainCheck,lf.GuardrailCheck,lf.GuardrailMainCheckTime\n" +
                "FROM tb_guardrail_fault lf ";
        if (actorIds.indexOf("1") >= 0) {//系统管理员
            if (faultStatus == 0) {//待审核
                sql += " WHERE (lf.GuardrailCheck =0) OR (lf.GuardrailMainCheck =0 AND lf.GuardrailCheck=1 )  ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE  lf.GuardrailMainCheck =1 AND lf.GuardrailCheck=1  ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE ( lf.GuardrailCheck =-1) OR (lf.GuardrailMainCheck =-1 AND lf.GuardrailCheck=1 ) ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("3") >= 0) {//审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE lf.GuardrailCheck=0 AND lf.GuardrailOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE lf.GuardrailCheck=1 AND lf.GuardrailOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE lf.GuardrailCheck=-1 AND lf.GuardrailOrgId=" + orgId + " ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("7") >= 0) {//主管审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE lf.GuardrailCheck=1 AND lf.GuardrailMainCheck=0 AND lf.GuardrailMainOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE lf.GuardrailCheck=1 AND lf.GuardrailMainCheck=1 AND lf.GuardrailMainOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE lf.GuardrailCheck=1 AND lf.GuardrailMainCheck=-1 AND lf.GuardrailMainOrgId=" + orgId + " ";
            } else {
                return null;
            }
        }
        sql += " ORDER BY lf.GuardrailFaultTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(GuardrailFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public Integer getGuardrailReportFaultCount4DT(Integer actorId, Integer userId, Integer orgId, String strSearch) throws Exception {
        String sql = "select count(*) " +
                "FROM (((tb_guardrail_fault gf JOIN tb_user u ON u.UserId=gf.GuardrailFaultCreatorId)\n" +
                "JOIN tb_org o ON o.OrgId=gf.GuardrailOrgId)JOIN tb_guardrail g ON g.GuardrailId=gf.GuardrailId)\n" +
                "WHERE concat(g.GuardrailRoadName,g.GuardrailStartRoad,g.GuardrailEndRoad)like '%" + strSearch + "%'";
        if (actorId == 3) {
            sql += " and gf.GuardrailOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and gf.GuardrailFaultCreatorId=" + userId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTGuardrailFaultDto> getGuardrailReportFault4DT(DTRequestDto dtRequestDto, Integer actorId, Integer userId, Integer orgId) throws Exception {
        String sql = "SELECT gf.*,u.UserName AS guardrailFaultCreatorName,u2.UserName AS guardrailMaintainerName,u3.UserName " +
                "AS guardrailMainCheckerName, o.OrgName AS guardrailOrgName,g.GuardrailPhoto from tb_guardrail_fault gf left join tb_user u on" +
                " u.UserId=gf.GuardrailFaultCreatorId left join tb_user u2 on u2.userId = gf.GuardrailMaintainerId " +
                "left join tb_user u3 on u3.userId=gf.GuardrailMainCheckerId left join tb_org o on o.orgId=gf.GuardrailOrgId " +
                "left join tb_guardrail g on g.GuardrailId=gf.GuardrailId " +
                "WHERE concat(g.GuardrailRoadName,g.GuardrailStartRoad,g.GuardrailEndRoad)like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorId == 3) {
            sql += " and gf.GuardrailOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and gf.GuardrailFaultCreatorId=" + userId + "";
        }
        sql += " AND gf.GuardrailCheck IN (0,1,2,-1) \n" +
                " order by find_in_set( gf.GuardrailCheck ,'0,2,1,-1') , gf.GuardrailFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTGuardrailFaultDto.class));
        List<DTGuardrailFaultDto> guardrailFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return guardrailFaultDtos;
    }


    public List<GuardrailFaultDto> getGuardrailFaultReportList4App(Integer orgId, String actorIds, Integer faultStatus, Integer count, Integer size) throws Exception {
        String sql = "SELECT lf.GuardrailFaultPhoto,lf.GuardrailId,lf.GuardrailFaultType,lf.GuardrailFaultReason,lf.GuardrailFaultId,lf.GuardrailFaultLevel,lf.GuardrailFaultTime,lf.GuardrailCheck,lf.GuardrailMainCheck\n" +
                "FROM tb_guardrail sl,tb_guardrail_fault lf\n" +
                "WHERE sl.GuardrailId=lf.GuardrailId\n";
        //-1:未通过；0：待审核；1：已通过
        if (faultStatus == -1) {
            sql += " AND (lf.GuardrailCheck=-1 OR lf.GuardrailMainCheck=-1) ";
        } else if (faultStatus == 0) {
            sql += " AND ((lf.GuardrailCheck=1 AND lf.GuardrailMainCheck=0) OR lf.GuardrailCheck = 0) ";
        } else if (faultStatus == 1) {
            sql += " AND lf.GuardrailMainCheck = 1 ";
        }
        if (actorIds.indexOf("1") >= 0) {
            sql += "";
        } else {
            sql += " AND lf.GuardrailOrgId = " + orgId + " ";
        }
        sql += "ORDER BY GuardrailFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(GuardrailFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public List<GuardrailFaultDto> getGuardrailFaultHandleList4App(Integer orgId, Integer maintenanceStatus, String actorIds, Integer count, Integer size) throws Exception {
        String sql = "SELECT mf.GuardrailFaultId,mf.GuardrailId,mf.GuardrailFaultPhoto,mf.GuardrailFaultType,mf.GuardrailFaultLevel," +
                "mf.GuardrailFaultReason,mf.GuardrailMaintenanceStatus,mf.GuardrailMainCheckTime,mf.GuardrailMaintenancePhoto," +
                "mf.GuardrailMaintenanceTime\n" +
                "FROM tb_guardrail_fault mf " +
                "WHERE mf.GuardrailMaintenanceStatus=" + maintenanceStatus + "";
        if (actorIds.indexOf("7") >= 0) {
            sql += " AND mf.GuardrailMainOrgId=" + orgId + "";
        } else if (actorIds.indexOf("5") >= 0) {
            sql += " AND mf.GuardrailOrgId=" + orgId + "";
        }
        sql += " ORDER BY mf.GuardrailMainCheckTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(GuardrailFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public List<GuardrailFaultDto> getGuardrailFaultList4App(Integer orgId, String actorIds, Integer faultStatus, Integer count, Integer size) throws Exception {
        String sql = "SELECT gf.GuardrailFaultPhoto,gf.GuardrailId,gf.GuardrailFaultType,gf.GuardrailFaultReason," +
                "gf.GuardrailFaultId,gf.GuardrailFaultLevel,gf.GuardrailFaultTime,gf.GuardrailCheck,gf.GuardrailMainCheck\n" +
                "FROM tb_guardrail c,tb_guardrail_fault gf\n" +
                "WHERE c.GuardrailId=gf.GuardrailId\n";
        //-1:未通过；0：待审核；1：已通过
        if (faultStatus == -1) {
            sql += " AND (gf.GuardrailCheck=-1 OR gf.GuardrailMainCheck=-1) ";
        } else if (faultStatus == 0) {
            sql += " AND ((gf.GuardrailCheck=1 AND gf.GuardrailMainCheck=0) OR gf.GuardrailCheck = 0) ";
        } else if (faultStatus == 1) {
            sql += " AND gf.GuardrailMainCheck = 1 ";
        }
        if (actorIds.indexOf("1") >= 0) {
            sql += "";
        } else {
            sql += " AND gf.GuardrailOrgId = " + orgId + " ";
        }
        sql += "ORDER BY gf.GuardrailFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(GuardrailFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public GuardrailFaultDto getGuardrailLatLng(Integer guardrailId) throws Exception {
        String sql = "SELECT GROUP_CONCAT(c.CoordinateLongitude) AS longitude, GROUP_CONCAT(c.CoordinateLatitude) AS latitude\n" +
                " FROM tb_coordinate c\n" +
                " WHERE c.CoordinateTypeId=:guardrailId AND c.CoordinateType=0\n" +
                " ORDER BY c.CoordinateOrder";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(GuardrailFaultDto.class));
        query.setParameter("guardrailId", guardrailId);
        return (GuardrailFaultDto) query.getSingleResult();
    }

    public List<GuardrailDto> getGuardrailCoordinates4Map(Integer orgId, Integer orgType, Integer actorId) throws Exception{
        String sql = "SELECT CoordinateTypeId as elementId, " +
                "GROUP_CONCAT(CoordinateLongitude order by CoordinateOrder) as longitude, " +
                "GROUP_CONCAT(CoordinateLatitude order by CoordinateOrder) as latitude, " +
                "GuardrailId, GuardrailPhoto " +
                "from tb_coordinate c, tb_guardrail g " +
                "where c.CoordinateTypeId=g.GuardrailId AND CoordinateType=0 ";
        if (actorId != 1) {
            if (orgType == 0)
                sql += "and CoordinateTypeId in(SELECT GuardrailId FROM tb_guardrail where guardrailOrgId=" + orgId + ") ";
            else if (orgType == 1)
                sql += "and CoordinateTypeId in(SELECT GuardrailId FROM tb_guardrail where guardrailMainOrgId=" + orgId + ") ";
        }
        sql += " GROUP BY CoordinateTypeId ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(GuardrailDto.class));
        return query.getResultList();
    }
}
